package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryMonthEndSubmitDao;
import com.boss.salary.vo.SalaryMonthEndVO;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@Repository
public class SalaryMonthEndSubmitDaoImpl extends BaseDaoImpl implements ISalaryMonthEndSubmitDao {
    @Override
    public void batchSaveSalaryMonthSubmit(List<SalaryMonthEndVO> salaryMonthEndVOList) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        if (salaryMonthEndVOList.size() > 0) {
            List<Object[]> paramList = new ArrayList<>(salaryMonthEndVOList.size());
            Class<?> clazz = SalaryMonthEndVO.class;
            for (SalaryMonthEndVO salaryMonthEndVO : salaryMonthEndVOList) {
                Object[] paramArr = {
                        SysUtils.generateGuid(), String.valueOf(DateUtils.getCurrentTimeStamp()), String.valueOf(DateUtils.getCurrentTimeStamp()),
                        salaryMonthEndVO.getAgencyId(), salaryMonthEndVO.getPersonId(), salaryMonthEndVO.getMofDivCode(), salaryMonthEndVO.getIsThirteen()
                };
                List<Object> params = new ArrayList<>(Arrays.asList(paramArr));
                for (int i = 1; i < 101; i++) {
                    Method getMethod = clazz.getDeclaredMethod("getS" + i);
                    params.add(getMethod.invoke(salaryMonthEndVO));
                }
                paramList.add(params.toArray());
            }
            StringBuilder sb = new StringBuilder();
            sb.append(" merge into ").append(TableName.SALARY_MONTH_END_SUBMIT)
                    .append("  a using " )
                    .append(" (select ? id, ? create_time, ? update_time, ? agency_id, ? person_id,? mof_div_code, ? is_thirteen, ? ")
                    .append(" s1, ? s2, ? s3, ? s4, ? s5, ? s6, ? s7, ? s8, ? s9, ? s10, ? s11, ? s12, ? s13, ? s14, ? s15, ? s16, ? s17, ? s18, ? s19, ? s20, ?  ")
                    .append(" s21, ? s22, ? s23, ? s24, ? s25, ? s26, ? s27, ? s28, ? s29, ? s30, ? s31, ? s32, ? s33, ? s34, ? s35, ? s36, ? s37, ? s38, ? s39, ? s40, ? ")
                    .append(" s41, ? s42, ? s43, ? s44, ? s45, ? s46, ? s47, ? s48, ? s49, ? s50, ? s51, ? s52, ? s53, ? s54, ? s55, ? s56, ? s57, ? s58, ? s59, ? s60, ? ")
                    .append(" s61, ? s62, ? s63, ? s64, ? s65, ? s66, ? s67, ? s68, ? s69, ? s70, ? s71, ? s72, ? s73, ? s74, ? s75, ? s76, ? s77, ? s78, ? s79, ? s80, ? ")
                    .append(" s81, ? s82, ? s83, ? s84, ? s85, ? s86, ? s87, ? s88, ? s89, ? s90, ? s91, ? s92, ? s93, ? s94, ? s95, ? s96, ? s97, ? s98, ? s99, ? s100 from  dual )  b ")
                    .append(" on (a.person_id=b.person_id and a.is_thirteen=b.is_thirteen and a.agency_id=b.agency_id) " )
                    .append(" WHEN MATCHED THEN " )
                    .append("  UPDATE SET  a.create_time=b.create_time, a.update_time=b.update_time, a.mof_div_code=b.mof_div_code, " )
                    .append(" a.s1=b.s1, a.s2=b.s2, a.s3=b.s3,a.s4=b.s4,a.s5=b.s5,a.s6=b.s6, a.s7=b.s7,a.s8=b.s8, a.s9=b.s9,a.s10=b.s10, a.s11=b.s11, a.s12=b.s12,a.s13=b.s13, a.s14=b.s14,a.s15=b.s15, a.s16=b.s16, a.s17=b.s17,a.s18=b.s18, a.s19=b.s19,a.s20=b.s20, ")
                    .append(" a.s21=b.s21, a.s22=b.s22, a.s23=b.s23,a.s24=b.s24, a.s25=b.s25,a.s26=b.s26, a.s27=b.s27, a.s28=b.s28,a.s29=b.s29, a.s30=b.s30,a.s31=b.s31, a.s32=b.s32, a.s33=b.s33,a.s34=b.s34, a.s35=b.s35,a.s36=b.s36, a.s37=b.s37, a.s38=b.s38,a.s39=b.s39, a.s40=b.s40, ")
                    .append(" a.s41=b.s41, a.s42=b.s42, a.s43=b.s43,a.s44=b.s44, a.s45=b.s45,a.s46=b.s46, a.s47=b.s47, a.s48=b.s48,a.s49=b.s49, a.s50=b.s50,a.s51=b.s51, a.s52=b.s52, a.s53=b.s53,a.s54=b.s54, a.s55=b.s55,a.s56=b.s56, a.s57=b.s57, a.s58=b.s58,a.s59=b.s59, a.s60=b.s60,  ")
                    .append(" a.s61=b.s61, a.s62=b.s62, a.s63=b.s63,a.s64=b.s64, a.s65=b.s65,a.s66=b.s66, a.s67=b.s67, a.s68=b.s68,a.s69=b.s69, a.s70=b.s70,a.s71=b.s71, a.s72=b.s72, a.s73=b.s73,a.s74=b.s74, a.s75=b.s75,a.s76=b.s76, a.s77=b.s77, a.s78=b.s78,a.s79=b.s79, a.s80=b.s80,  ")
                    .append(" a.s81=b.s81, a.s82=b.s82, a.s83=b.s83,a.s84=b.s84, a.s85=b.s85,a.s86=b.s86, a.s87=b.s87, a.s88=b.s88,a.s89=b.s89, a.s90=b.s90,a.s91=b.s91, a.s92=b.s92, a.s93=b.s93,a.s94=b.s94, a.s95=b.s95,a.s96=b.s96, a.s97=b.s97, a.s98=b.s98,a.s99=b.s99, a.s100=b.s100  ")
                    .append( " WHEN NOT MATCHED THEN " )
                    .append( "   INSERT " )
                    .append(" ( a.id, a.create_time, a.update_time, a.agency_id, a.person_id, a.is_thirteen,a.mof_div_code, ")
                    .append(" a.s1, a.s2, a.s3, a.s4, a.s5, a.s6, a.s7, a.s8, a.s9, a.s10, a.s11, a.s12, a.s13, a.s14, a.s15, a.s16, a.s17, a.s18, a.s19, a.s20,  ")
                    .append(" a.s21, a.s22, a.s23, a.s24, a.s25, a.s26, a.s27, a.s28, a.s29, a.s30, a.s31, a.s32, a.s33, a.s34, a.s35, a.s36, a.s37, a.s38, a.s39, a.s40, ")
                    .append(" a.s41, a.s42, a.s43, a.s44, a.s45, a.s46, a.s47, a.s48, a.s49, a.s50, a.s51, a.s52, a.s53, a.s54, a.s55, a.s56, a.s57, a.s58, a.s59, a.s60, ")
                    .append(" a.s61, a.s62, a.s63, a.s64, a.s65, a.s66, a.s67, a.s68, a.s69, a.s70, a.s71, a.s72, a.s73, a.s74, a.s75, a.s76, a.s77, a.s78, a.s79, a.s80, ")
                    .append(" a.s81, a.s82, a.s83, a.s84, a.s85, a.s86, a.s87, a.s88, a.s89, a.s90, a.s91, a.s92, a.s93, a.s94, a.s95, a.s96, a.s97, a.s98, a.s99, a.s100 ) ")
                    .append( "   VALUES " )
                    .append(" ( b.id, b.create_time, b.update_time, b.agency_id, b.person_id, b.is_thirteen,b.mof_div_code, ")
                    .append(" b.s1, b.s2, b.s3, b.s4, b.s5, b.s6, b.s7, b.s8, b.s9, b.s10, b.s11, b.s12, b.s13, b.s14, b.s15, b.s16, b.s17, b.s18, b.s19, b.s20,  ")
                    .append(" b.s21, b.s22, b.s23, b.s24, b.s25, b.s26, b.s27, b.s28, b.s29, b.s30, b.s31, b.s32, b.s33, b.s34, b.s35, b.s36, b.s37, b.s38, b.s39, b.s40, ")
                    .append(" b.s41, b.s42, b.s43, b.s44, b.s45, b.s46, b.s47, b.s48, b.s49, b.s50, b.s51, b.s52, b.s53, b.s54, b.s55, b.s56, b.s57, b.s58, b.s59, b.s60, ")
                    .append(" b.s61, b.s62, b.s63, b.s64, b.s65, b.s66, b.s67, b.s68, b.s69, b.s70, b.s71, b.s72, b.s73, b.s74, b.s75, b.s76, b.s77, b.s78, b.s79, b.s80, ")
                    .append(" b.s81, b.s82, b.s83, b.s84, b.s85, b.s86, b.s87, b.s88, b.s89, b.s90, b.s91, b.s92, b.s93, b.s94, b.s95, b.s96, b.s97, b.s98, b.s99, b.s100 ) ");

            super.getJdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
                public int getBatchSize() {
                    return paramList.size();
                    //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
                }
                public void setValues(PreparedStatement ps, int i)throws SQLException {
                    Object[] sme = paramList.get(i);
                    for(int a = 0; a < 107; a++){
                        ps.setObject(a+1,sme[a]);
                    }

                }
            });
        }
    }

    @Override
    public void batchPhysicalDeleteSalaryMonthSubmit(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.SALARY_MONTH_END_SUBMIT);

//            sb.append(" where  mof_div_code = ?");
//        if(!CollectionUtils.isEmpty(agencyIds)){
//            sb.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
//        }

        super.getjdbcTemplate().update(sb.toString());//,province
    }
}
